﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace MoiGioiHDBank.DAO
{
    public class SQLDataAccessHelper
    {
        public static String ConnectionString
        {
            get
            {
                string con = ConfigurationManager.ConnectionStrings["MoiGioiConnectionString"].ConnectionString;
                return con;
            }
        }
        private DbProviderFactory factory;
        public SQLDataAccessHelper()
        {
            factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
        }
        public DbParameter DoiSo()
        {
            return factory.CreateParameter();
        }
        private DataSet DienVaoDataSet(SqlCommand cmd, List<DbParameter> listParams)
        {
            DataSet ds = new DataSet();
            try
            {
                SqlConnection con = new SqlConnection();
                con.ConnectionString = ConnectionString;
                cmd.Connection = con;
                try
                {
                    con.Open();
                    if (listParams != null)
                    {
                        foreach (DbParameter para in listParams)
                        {
                            cmd.Parameters.Add(para);
                        }
                    }
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(ds);
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    con.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }
        public DataTable ThucThiTruyVan(SqlCommand cmd, List<DbParameter> listParams)
        {
            DataTable dt = new DataTable();
            DataSet ds = DienVaoDataSet(cmd, listParams);
            if (ds != null && ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];
            }
            return dt;
        }
        public DataTable ThucThiTruyVan(SqlCommand cmd)
        {
            return ThucThiTruyVan(cmd, null);
        }
        public int ThucThiKhongTruyVan(SqlCommand cmd, List<DbParameter> listParams)
        {
            int n = -1;
            SqlConnection con = new SqlConnection(ConnectionString);
            cmd.Connection = con;
            try
            {
                con.Open();
                try
                {
                    if (listParams != null)
                    {
                        foreach (DbParameter para in listParams)
                        {
                            cmd.Parameters.Add(para);
                        }
                    }
                    n = cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    con.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return n;
        }
        public int ThucThiKhongTruyVan(SqlCommand cmd)
        {
            return ThucThiKhongTruyVan(cmd, null);
        }
    }
}